package com.hiwan.system.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.hssf.util.Region;

/**
 */
public class ExcelUtil {
	/**
	 * 取出单元格String类型的值
	 * @param cell
	 * @return
	 */
	public static String getCellStringValue(Cell cell) {
		String cellValue = "";
		String dateFormat = "yyyy-MM-dd";
		if (cell == null) {
			return cellValue;
		}
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:// 字符串类型
			cellValue = cell.getStringCellValue().toString();
			if (cellValue.trim().equals("") || cellValue.trim().length() <= 0)
				cellValue = "";
			break;
		case Cell.CELL_TYPE_NUMERIC: // 数值类型(包含日期)
			if (DateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				cellValue = new SimpleDateFormat(dateFormat).format(date);
			} else {
				cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_FORMULA: // 公式

			try {
				cellValue = String.valueOf(cell.getStringCellValue());
			} catch (IllegalStateException e) {
				cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BLANK:
			cellValue = "";
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			break;
		case Cell.CELL_TYPE_ERROR:
			break;
		default:
			cellValue = "";
			break;
		}
		return cellValue.trim();
	}

	/**
	 * 将EXCEL中A,B,C,D,E列映射成0,1,2,3
	 * 
	 * @param col
	 */
	public static int getExcelCol(String col) {
		col = col.toUpperCase();
		// 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
		int count = -1;
		char[] cs = col.toCharArray();
		for (int i = 0; i < cs.length; i++) {
			count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
		}
		return count;
	}

	/**
	 * 设置单元格上提示
	 * 
	 * @param sheet
	 *            要设置的sheet.
	 * @param promptTitle
	 *            标题
	 * @param promptContent
	 *            内容
	 * @param firstRow
	 *            开始行
	 * @param endRow
	 *            结束行
	 * @param firstCol
	 *            开始列
	 * @param endCol
	 *            结束列
	 * @return 设置好的sheet.
	 */
	public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, int firstRow,
			int endRow, int firstCol, int endCol) {
		// 构造constraint对象
		DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");
		// 四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint);
		data_validation_view.createPromptBox(promptTitle, promptContent);
		sheet.addValidationData(data_validation_view);
		return sheet;
	}

	/**
	 * 设置某些列的值只能输入预制的数据,显示下拉框.
	 * 
	 * @param sheet
	 *            要设置的sheet.
	 * @param textlist
	 *            下拉框显示的内容
	 * @param firstRow
	 *            开始行
	 * @param endRow
	 *            结束行
	 * @param firstCol
	 *            开始列
	 * @param endCol
	 *            结束列
	 * @return 设置好的sheet.
	 */
	public static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow,
			int firstCol, int endCol) {
		// 加载下拉列表内容
		DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
		// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
		sheet.addValidationData(data_validation_list);
		return sheet;
	}



	/**
	 * 
	 * @param fromSheet
	 * @param cloneExcel
	 * @return
	 * @throws Exception
	 */
	public static HSSFWorkbook copyExcelSheet(HSSFSheet fromSheet, HSSFWorkbook cloneExcel) throws Exception {
		HSSFSheet toSheet = cloneExcel.createSheet(fromSheet.getSheetName());
		MergerRegion(toSheet, fromSheet);

		int firstRow = fromSheet.getFirstRowNum();
		int lastRow = fromSheet.getLastRowNum();

		for (int i = firstRow; i <= lastRow; i++) {
			// 创建新建excel Sheet的行
			HSSFRow rowCreat = toSheet.createRow(i);
			// 取得源有excel Sheet的行
			HSSFRow row = fromSheet.getRow(i);
			if (null != row) {
				int firstCell = row.getFirstCellNum();
				int lastCell = row.getLastCellNum();

				for (int j = firstCell; j < lastCell; j++) {
					rowCreat.createCell(j);
					String strVal = "";
					// 复制单元格样式
					if (row.getCell(j) == null) {
						continue;
					} else {
						HSSFCellStyle oldStely = row.getCell(j).getCellStyle();
						//rowCreat.getCell(j).setCellStyle(copyStyle(oldStely, HSSFCellStyle));
						strVal = removeInternalBlank(ExcelUtil.getCellStringValue(row.getCell(j)));
					}
					rowCreat.getCell(j).setCellValue(strVal);
				}
			}
		}
		return cloneExcel;
	}

	/**
	 * 克隆Excel
	 * @param fromPath
	 * @param toPath
	 * @return
	 */
	public static boolean cloneExcel(String fromPath, String toPath) {
		try {
			HSSFWorkbook cloneExcel = new HSSFWorkbook();
			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(fromPath));
			
			for (int i = 0; i < wb.getNumberOfSheets(); i++) {
				HSSFSheet fromSheet = wb.getSheetAt(i);
				cloneExcel = copyExcelSheet(fromSheet, cloneExcel);
			}
			
			FileOutputStream fileOut = new FileOutputStream(toPath);
			cloneExcel.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * 克隆Excel-通过删除不需要的sheet方式复制
	 * @param fromPath
	 * @param toPath
	 * @param maps
	 * @return
	 */
	public static boolean delPartSheetFromExcel(String fromPath, String toPath, List<Integer> sheetList) {
		try {
			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(fromPath));
			
			for (int i = 0; i < sheetList.size(); i++) {
				wb.removeSheetAt(sheetList.get(i));
			}
			
			File toFile = new File(toPath);

			if (!toFile.getParentFile().exists() && !toFile.getParentFile().mkdirs()) {
				throw new Exception("Can not create the directory for file : [" + toFile.getPath() + "]");
			}
			
			FileOutputStream fileOut = new FileOutputStream(toPath);
			wb.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}


	/**
	 * 克隆Excel-复制需要的sheet页，复制后样式有变化
	 * @param fromPath
	 * @param toPath
	 * @param excelName
	 * @param maps
	 * @return
	 */
	public static boolean clonePartSheetFromExcel(String fromPath, String toPath, List<Integer> sheetList) {
		try {
			HSSFWorkbook cloneExcel = new HSSFWorkbook();
			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(fromPath));
			
			for (int i = 0; i < sheetList.size(); i++) {
				HSSFSheet fromSheet = wb.getSheetAt(sheetList.get(i));
				cloneExcel = copyExcelSheet(fromSheet, cloneExcel);
			}
			
			FileOutputStream fileOut = new FileOutputStream(toPath);
			cloneExcel.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	* 复制单元格的样式
	* @param oldStely
	* @return newStyle
	*/
	private static HSSFCellStyle copyStyle(HSSFCellStyle oldStely, HSSFCellStyle newStyle) {
		// 单元格样式
		newStyle.cloneStyleFrom(oldStely);
		return newStyle;
	}

	/**
	 * 去除字符串内部空格
	 */
	public static String removeInternalBlank(String s) {
		// System.out.println("bb:" + s);
		Pattern p = Pattern.compile("\\s*|\t|\r|\n");
		Matcher m = p.matcher(s);
		char str[] = s.toCharArray();
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < str.length; i++) {
			if (str[i] == ' ') {
				sb.append(' ');
			} else {
				break;
			}
		}
		String after = m.replaceAll("");
		return sb.toString() + after;
	}

	/**
	 * 复制原有sheet的合并单元格到新创建的sheet
	 *
	 * @param sheetCreat
	 *            新创建sheet
	 * @param sheet
	 *            原有的sheet
	 */
	private static void MergerRegion(HSSFSheet sheetCreat, HSSFSheet sheet) {
		int sheetMergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergerCount; i++) {
			Region mergedRegionAt = sheet.getMergedRegionAt(i);
			sheetCreat.addMergedRegion(mergedRegionAt);
		}
	}
}
